package_name = "mysql-connector-python"

import mysql.connector
from mysql.connector import Error


class MySQLConnectionManager:
    def __init__(self, host, user, password, database, port=3306):
        """
        初始化MySQL连接管理器。
        参数:
        - host (str): MySQL服务器主机名或IP地址
        - user (str): 登录用户名
        - password (str): 登录密码
        - database (str): 要连接的数据库名称
        """
        self.host = host
        self.user = user
        self.port = port
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self):
        """
        建立到MySQL数据库的连接并创建游标。

        异常:
        - Raises mysql.connector.Error if unable to connect.
        """
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database
            )
            self.cursor = self.connection.cursor(dictionary=True)  # 使用字典模式返回查询结果
        except Error as e:
            print(f"Error connecting to MySQL: {e}")
            raise

    def disconnect(self):
        """
        关闭当前的数据库连接和游标。
        """
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

    def execute_query(self, query, params=None):
        """
        执行SQL查询。

        参数:
        - query (str): SQL查询语句
        - params (tuple or list, optional): 查询参数，如果查询需要的话

        返回:
        - cursor.fetchall()的结果，即查询结果列表（每一项为字典形式）

        异常:
        - Raises mysql.connector.Error if a database error occurs.
        """
        if not self.cursor or not self.connection.is_connected():
            self.connect()

        try:
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)
            results = self.cursor.fetchall()
            return results
        except Error as e:
            print(f"Error executing query: {e}")
            raise

    def executeNonQuery(self, query, params=None):
        print(query)
        """
        执行非查询型SQL语句（如INSERT, UPDATE, DELETE等）。

        参数:
        - query (str): SQL非查询语句
        - params (tuple or list, optional): 语句参数，如果语句需要的话

        返回:
        - 受影响的行数

        异常:
        - Raises mysql.connector.Error if a database error occurs.
        """
        if not self.cursor or not self.connection.is_connected():
            self.connect()

        try:
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)
            self.connection.commit()  # 提交事务
            return self.cursor.rowcount
        except Error as e:
            self.connection.rollback()  # 发生错误时回滚事务
            print(f"Error executing non-query: {e}")
            raise


if __name__ == '__main__':
    # 使用示例
    connection_manager = MySQLConnectionManager(
        host='127.0.0.1',
        user='root',
        password='123456',
        database='cmxx',
        port=3307
    )
    try:
        connection_manager.connect()
        result = connection_manager.execute_query("SELECT * FROM sheet1")
        print(result)

        affected_rows = connection_manager.executeNonQuery(
            "UPDATE sheet1 SET column_name = %s WHERE condition_column = %s",
            ('new_value', 'condition_value')
        )
        print(f"Affected rows: {affected_rows}")

    finally:
        connection_manager.disconnect()
